<?php

namespace Chat\Model;


use Zend\Db\Adapter\Adapter;
use Zend\Db\TableGateway\AbstractTableGateway;
use Zend\Db\Sql\Select;
use Zend\Db\Sql\Sql;
use Zend\Db\Sql\Where;

class ChatTable extends AbstractTableGateway 
{
	
	protected $table = 'chat';
	
public function __construct(Adapter $adapter) {
        $this->adapter = $adapter;
    }
    
    public function saveChatMessage(Entity\ChatMessage $chatMessage) {
    	
    	$data = array(
    			'message' => $chatMessage->getText(),
    			'receiver_ID' => $chatMessage->getReceiverID(),
    			'sender_ID' => $chatMessage->getSenderID(),
    	);
    	
    	$this->insert($data);
    }
    
    public function fetchAll() {    	
    	
    $resultSet = $this->select(function (Select $select) {
                    $select->order('posted_on ASC');
                });
        $entities = array();
        foreach ($resultSet as $row) {
            $entity = new Entity\ChatMessage();
            $entity->setID($row->ID)
                    ->setText($row->text)
                    ->setSenderID($row->senderID)
            ->setReceiverID($row->receiverID);
            $entities[] = $entity;
        }
        return $entities;
    }
    
    public function fetchLatestMsgBetweenUsers($senderID,$receiverID){
    	    	  $query = "SELECT a.msgID, b.email, a.message, a.posted_on FROM (SELECT ID as msgID, sender_ID, receiver_ID, message, posted_on FROM chat WHERE new = 1 AND sender_ID = ".$senderID." AND receiver_ID = ".$receiverID.") as a 
		LEFT JOIN user as b ON a.sender_ID = b.user_id ORDER BY posted_on";
    	    	  
    	    	  $stmt = $this->adapter->query($query);
    	    	  $resultSet = $stmt->execute();
    	    	  
    	    	  

    	    	  //if (!$resultSet)
    	    	 // 	return false;
    	    	  
    	    	  $entities = array();
    	    	  foreach ($resultSet as $row) {
    	    	  	
    	    	  	if ($row['msgID'] == null){
    	    	  		$entities['-1'] = array($row['email'] => $row['message']);
    	    	  		
    	    	  	}else{
    	    	  		$entities[$row['msgID']] = array($row['email'] => $row['message']);
    	    	  		\FB::info('fetchLatestMsgBetweenUsers '. key($entities));
    	    	  	}
    	    	  
    	    	  	
    	    	  	
    	    	  }
    	    	  
    	    	  $query = null;
    	    	  $stmt = null;
    	    	  $resultSet = null;
    	    	  
    	    	  $query = "UPDATE chat SET new = 0 WHERE new = 1 AND sender_ID = ".$senderID." AND receiver_ID = ".$receiverID;
    	    	  
    	    	  $stmt = $this->adapter->query($query);
    	    	  $resultSet = $stmt->execute();
    	    	 
    	    	  return $entities;
    	    	  
    	
    }
    
    public function getMessagesBetweenUsers($userID,$myID) {
    	$query = "SELECT a.ID, b.email, a.message, a.posted_on FROM (SELECT ID, sender_ID, message, posted_on FROM
   chat WHERE sender_ID = ".$myID." AND receiver_ID = ".$userID." UNION SELECT ID, 
   		sender_ID, message, posted_on FROM chat WHERE sender_ID = ".$userID." AND 
   				receiver_ID = ".$myID." ) as a LEFT JOIN user as b ON a.sender_ID = b.user_id ORDER BY posted_on";
    	
    	$stmt = $this->adapter->query($query);
    	$resultSet = $stmt->execute();
    	   
    	if (!$resultSet)
    		return false;
    	$entities = array();
    	
    	foreach ($resultSet as $row) {
            
            $entities[$row['ID']] = array($row['email'] =>$row['message']);
        }
        
        $stmt = null;
        $resultSet = null;
        $query = null;
        
        $query = "SELECT MAX(ID) as msgID, sender_ID, receiver_ID, message, posted_on FROM chat WHERE sender_ID = ".$userID." AND receiver_ID = ".$myID;
                
        $stmt = $this->adapter->query($query);
        $resultSet = $stmt->execute();
      
        foreach ($resultSet as $row) {
        	\FB::info('Row '.$row['msgID']);
        	$entities['lastMsg'] = $row['msgID'];
        }
        
        $query = null;
        $stmt = null;
        $resultSet = null;
        
        $query = "UPDATE chat SET new = 0 WHERE new = 1 AND sender_ID = ".$userID." AND receiver_ID = ".$myID;
        
        $stmt = $this->adapter->query($query);
        $resultSet = $stmt->execute();
        
        
        return $entities;
    }
    
    public function getLastMessageID($senderID,$receiverID)
    {
    	
        
        $query = "SELECT a.msgID, b.email, a.message, a.posted_on FROM
		(SELECT MAX(ID) as msgID, sender_ID, message, posted_on FROM chat WHERE sender_ID = $senderID AND receiver_ID = $receiverID ) as a
		LEFT JOIN user as b ON a.sender_ID = b.user_id ORDER BY b.email";
     
        $stmt = $this->adapter->query($query);
        $resultSet = $stmt->execute();
        
        if (!$resultSet)
        	return false;
       
        $entities = array(); 
        foreach ($resultSet as $row) {
        	
        if ($row['msgID'] == null){
        	$entities['msgID'] = -1;
        }else{
        	$entities['msgID'] = $row['msgID'];
        }
        	
        	
        }
        \FB::info('getLastMessageID '.$entities['msgID']);
        return $entities;    
    	
    }
    
    
    
    
    
    
}